// 打造一个可以链接数据库的方法

// 导入模块
const mysql = require('mysql2/promise');
const { database } = require('../config/index.js');

// 创建连接池
const pool = mysql.createPool({
    host: database.HOST,
    user: database.USERNAME,
    password: database.PASSWORD,
    database: database.DATABASE,
    port: database.PORT
})

// 可以连接数据库的方法
const allService = {
    async query(sql) {
        try {
            // 通过连接池来创建连接
            const conn = await pool.getConnection()
            // 对该连接执行某些操作
            const [rows, err] = await conn.query(sql)
            
            // 不要忘记释放连接！
            pool.releaseConnection(conn);

            return Promise.resolve(rows)
        } catch (error) {
            return Promise.reject(error)
        }
        
    }
}

// 登录接口
const userLogin = (username, password) => {
    const _sql = `select * from users where username="${username}" and password="${password}";`
    return allService.query(_sql)
}

// 查找账号
const userFind = (username) => {
    const _sql = `select * from users where username="${username}";`
    return allService.query(_sql)
}

// 注册
const userRegister =  (values) => {
    const _sql = `insert into users (username, password, nickname) values ("${values.username}", "${values.password}", "${values.nickname}");`
    return allService.query(_sql)
}

// 根据分类查找数据
const findNoteListByType = (note_type, id) => {
    const _sql = `select * from note where note_type="${note_type}" and userId="${id}"`;
    return allService.query(_sql)
    
}

// 根据id查找数据
const finNoteDetailById = (id) => {
    const _sql = `select * from note where id="${id}"`;
    return allService.query(_sql)
}

// 发布笔记
const notePublish = (values) => {
    const _sql = `insert into note (
            title, 
            note_content, 
            head_img, 
            note_type, 
            c_time, 
            m_time, 
            userId,
            nickname
        ) values (
            "${values.title}", 
            "${values.note_content}", 
            "${values.head_img}", 
            "${values.note_type}", 
            "${values.c_time}", 
            "${values.m_time}", 
            "${values.userId}", 
            "${values.nickname}");`
            return allService.query(_sql)
}

module.exports = { 
    userLogin,
    userFind,
    userRegister,
    findNoteListByType,
    finNoteDetailById,
    notePublish

}